﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;
/// <summary>
/// Summary description for ListAndProcessData
/// </summary>
public class ListAndProcessData
{
	public ListAndProcessData()
	{
	}
    
    int idcus = 0,idemployee = 0;
    string cusname = null, employeename = null;
    string loginName = null;
    string password = null;
    string sex = null;
    string phone = null;
    string address = null;
    string email = null;
    int age = 0 ;
    int indetitycard = 0;
    string qualification = null;
    DateTime createDate;

    LinQDataDataContext da = new LinQDataDataContext();

    public List<TravelPlan> getAllTravelPlan()
    {     
        var TravelPlan = da.TravelPlans.Select(c => c).ToList();
        return TravelPlan;
    }

    public List<Customers> getAllCustomer(){
        
        var a = from t in da.Customers
                select new {
                    t.IdCustomer,
                    t.FullName,
                    t.Sex,
                    t.Age,
                    t.Address,
                    t.Email,
                    t.Phone,
                    t.IndentityCard,

                };
        List<Customers> lsit = new List<Customers>();
        foreach (var item in a)
	    {
		    idcus = item.IdCustomer;
            cusname = item.FullName;
            if (item.Sex == 0)
	        {
		        sex = "Male";
	        }else
	        {
                sex = "Female";
	        }
            phone = item.Phone;
            address = item.Address;
            email = item.Email;
            age = Convert.ToInt32(item.Age);
            indetitycard = Convert.ToInt32(item.IndentityCard);

            lsit.Add(new Customers(idcus, cusname, age, sex, address, email, phone, indetitycard));
	    }
        return lsit;
     }

    public List<Employees> getAllEmployee()
    {
        var a = from t in da.Employees
                select new
                {
                    t.LoginName,
                    t.IdEmployee,
                    t.FullName,
                    t.Sex,
                    t.Age,
                    t.Address,
                    t.Phone,
                    t.Email,
                    t.Qualification,
                    t.Create_Date
                };
        List<Employees> employeeList = new List<Employees>();
        foreach (var item in a)
        {
            idemployee = item.IdEmployee;
            loginName = item.LoginName;
            employeename = item.FullName;
            if (item.Sex == 0)
            {
                sex = "Male";
            }
            else
            {
                sex = "Female";
            }
            address = item.Address;
            phone = item.Phone;
            email = item.Email;
            age = Convert.ToInt32(item.Age);
            qualification = item.Qualification;
            createDate = Convert.ToDateTime(item.Create_Date);


            employeeList.Add(new Employees(idemployee, loginName, employeename, age, sex, address, email, phone, qualification, createDate));
        }
        return employeeList;
    }

    public List<GroupAccount> getAllGroupAccount()
    {
        var groupAccount = da.GroupAccounts.Select(c => c).ToList();
        return groupAccount;
    }

    public Boolean UpdateCustomer(String id, string fullNameCustomer, String age,int sex, String address, String phone, String email, String identityCard)
    {
            Customer customer = da.Customers.Single(c => c.IdCustomer == int.Parse(id));
            customer.FullName = fullNameCustomer;
            customer.Age = int.Parse(age);
            customer.Sex = sex;
            customer.Address = address;
            customer.Phone = phone;
            customer.Email = email;
            customer.IndentityCard = int.Parse(identityCard);

            da.SubmitChanges();
            return true;

    }

    public Boolean UpdateEmployee(String id, String loginName, String fullNameEmployee, String age, int sex, String address, String phone, String email, String qualification)
    {
        Employee employee = da.Employees.Single(c => c.IdEmployee == int.Parse(id));
        employee.LoginName = loginName;
        employee.FullName = fullNameEmployee;
        employee.Age = int.Parse(age);
        employee.Sex = sex;
        employee.Address = address;
        employee.Phone = phone;
        employee.Email = email;
        employee.Qualification = qualification;

        da.SubmitChanges();
        return true;
    }

    public Boolean InsertCustomer(string fullNameCustomer, String age, int sex, String address, String phone, String email, String identityCard)
    {
        try
        {
            Customer customer = new Customer();
            customer.FullName = fullNameCustomer;
            customer.Age = int.Parse(age);
            customer.Sex = sex;
            customer.Address = address;
            customer.Phone = phone;
            customer.Email = email;
            customer.IndentityCard = int.Parse(identityCard);
            
            da.Customers.InsertOnSubmit(customer);
            
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }

    }

    public Boolean InsertEmployee(String loginName, String password, String fullNameEmployee, String age, int sex, String address, String phone, String email, String qualification)
    {
        ListAndProcessData ws = new ListAndProcessData();
        try
        {
            Employee employee = new Employee();
            employee.LoginName = loginName;
            employee.Password = ws.getMD5Password(password);
            employee.FullName = fullNameEmployee;
            employee.Age = int.Parse(age);
            employee.Sex = sex;
            employee.Address = address;
            employee.Phone = phone;
            employee.Email = email;
            employee.Qualification = qualification;
            employee.Create_Date = DateTime.Now;
            
            da.Employees.InsertOnSubmit(employee);

            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;
        }
    }

    public Boolean DeleteCustomer(int id)
    {
        bool check = false;

        try
        {
            var a = da.Customers.Where(c => c.IdCustomer == id).ToList();
            foreach (var del in a)
            {
                da.Customers.DeleteOnSubmit(del);

            }
            check = true;
        }
        catch
        {
            check = false;
        }
        da.SubmitChanges();
        return check;
    }

    public Boolean DeleteEmployee(int id)
    {
        bool check = false;

        try
        {
            var a = da.Employees.Where(c => c.IdEmployee == id).ToList();
            foreach (var del in a)
            {
                da.Employees.DeleteOnSubmit(del);

            }
            check = true;
        }
        catch
        {
            check = false;
        }
        da.SubmitChanges();
        return check;
    }

    public List<Customers> searchCustomer(string customerName, string identitycard)
    {

        var search1 = (from t in da.Customers
                      where
                       t.FullName.StartsWith(customerName) &&
                       t.IndentityCard.ToString().StartsWith(identitycard)
                      select new
                      {
                          t.IdCustomer,
                          t.FullName,
                          t.Age,
                          t.Sex,
                          t.Address,
                          t.Phone,
                          t.Email,    
                          t.IndentityCard,

                      }).Distinct();
        List<Customers> searchCustomer = new List<Customers>();
        if (customerName.Length > 0 && indetitycard.ToString().Length > 0)
        {
            foreach (var a in search1)
            {
                idcus = a.IdCustomer;
                cusname = a.FullName;
                age = Convert.ToInt32(a.Age);
                if (a.Sex == 0)
                {
                    sex = "Male";
                }
                else
                {
                    sex = "Female";
                }
                address = a.Address;
                phone = a.Phone;
                email = a.Email;
                indetitycard = Convert.ToInt32(a.IndentityCard);

                searchCustomer.Add(new Customers(idcus, cusname, age, sex, address, email, phone, indetitycard));

            }
        }
        //Search following 2 elements

        var search2 = (from t in da.Customers
                       where
                        t.FullName.StartsWith(customerName)
                       select new
                       {
                           t.IdCustomer,
                           t.FullName,
                           t.Age,
                           t.Sex,
                           t.Address,
                           t.Phone,
                           t.Email,
                           t.IndentityCard,

                       }).Distinct();
        if (customerName.Length > 0 && indetitycard.Equals(""))
        {
            foreach (var a in search2)
            {
                idcus = a.IdCustomer;
                cusname = a.FullName;
                age = Convert.ToInt32(a.Age);
                if (a.Sex == 0)
                {
                    sex = "Male";
                }
                else
                {
                    sex = "Female";
                }
                address = a.Address;
                phone = a.Phone;
                email = a.Email;
                indetitycard = Convert.ToInt32(a.IndentityCard);

                searchCustomer.Add(new Customers(idcus, cusname, age, sex, address, email, phone, indetitycard));

            }
        }
        //Search following customerName

        var search3 = (from t in da.Customers
                      where
                       t.IndentityCard.ToString().StartsWith(identitycard)
                      select new
                      {
                          t.IdCustomer,
                          t.FullName,
                          t.Age,
                          t.Sex,
                          t.Address,
                          t.Phone,
                          t.Email,     
                          t.IndentityCard,

                      }).Distinct();
        if (identitycard.Length > 0 && customerName.Equals(""))
        {
            foreach (var a in search3)
            {
                idcus = a.IdCustomer;
                cusname = a.FullName;
                age = Convert.ToInt32(a.Age);
                if (a.Sex == 0)
                {
                    sex = "Male";
                }
                else
                {
                    sex = "Female";
                }
                address = a.Address;
                phone = a.Phone;
                email = a.Email;
                indetitycard = Convert.ToInt32(a.IndentityCard);

                searchCustomer.Add(new Customers(idcus, cusname, age, sex, address, email, phone, indetitycard));

            }
        }

        return searchCustomer;
    }


    public List<Employees> searchEmployee(string employeeName, string email)
    {

        var search1 = (from t in da.Employees
                       where
                        t.FullName.StartsWith(employeeName) &&
                        t.Email.StartsWith(email)
                       select new
                       {
                           t.LoginName,
                           t.IdEmployee,
                           t.FullName,
                           t.Sex,
                           t.Age,
                           t.Address,
                           t.Phone,
                           t.Email,
                           t.Qualification,
                           t.Create_Date

                       }).Distinct();
        List<Employees> searchEmployee = new List<Employees>();
        if (employeeName.Length > 0 && email.Length > 0)
        {
            foreach (var a in search1)
            {
                idemployee = a.IdEmployee;
                loginName = a.LoginName;
                employeename = a.FullName;
                age = Convert.ToInt32(a.Age);
                if (a.Sex == 0)
                {
                    sex = "Male";
                }
                else
                {
                    sex = "Female";
                }
                address = a.Address;
                phone = a.Phone;
                email = a.Email;
                qualification = a.Qualification;
                createDate = Convert.ToDateTime(a.Create_Date);

                searchEmployee.Add(new Employees(idemployee, loginName, employeename, age, sex, address, email, phone, qualification, createDate));

            }
        }
        //Search following 2 elements

        var search2 = (from t in da.Employees
                       where
                        t.FullName.StartsWith(employeeName)
                       select new
                       {
                           t.LoginName,
                           t.IdEmployee,
                           t.FullName,
                           t.Sex,
                           t.Age,
                           t.Address,
                           t.Phone,
                           t.Email,
                           t.Qualification,
                           t.Create_Date

                       }).Distinct();
        if (employeeName.Length > 0 && email.Equals(""))
        {
            foreach (var a in search2)
            {
                idemployee = a.IdEmployee;
                loginName = a.LoginName;
                employeename = a.FullName;
                age = Convert.ToInt32(a.Age);
                if (a.Sex == 0)
                {
                    sex = "Male";
                }
                else
                {
                    sex = "Female";
                }
                address = a.Address;
                phone = a.Phone;
                email = a.Email;
                qualification = a.Qualification;
                createDate = Convert.ToDateTime(a.Create_Date);

                searchEmployee.Add(new Employees(idemployee, loginName, employeename, age, sex, address, email, phone, qualification, createDate));

            }
        }
        //Search following customerName

        var search3 = (from t in da.Employees
                       where
                        t.Email.StartsWith(email)
                       select new
                       {
                           t.LoginName,
                           t.IdEmployee,
                           t.FullName,
                           t.Sex,
                           t.Age,
                           t.Address,
                           t.Phone,
                           t.Email,
                           t.Qualification,
                           t.Create_Date

                       }).Distinct();
        if (email.Length > 0 && employeeName.Equals(""))
        {
            foreach (var a in search3)
            {
                idemployee = a.IdEmployee;
                loginName = a.LoginName;
                employeename = a.FullName;
                age = Convert.ToInt32(a.Age);
                if (a.Sex == 0)
                {
                    sex = "Male";
                }
                else
                {
                    sex = "Female";
                }
                address = a.Address;
                phone = a.Phone;
                email = a.Email;
                qualification = a.Qualification;
                createDate = Convert.ToDateTime(a.Create_Date);

                searchEmployee.Add(new Employees(idemployee, loginName, employeename, age, sex, address, email, phone, qualification, createDate));

            }
        }

        return searchEmployee;
    }

    //public Boolean validateCoincisiveLoginName(string coincideLoginName)
    //{
    //    bool isValid = false;
    //    var a = da.Employees.Select(c => c.LoginName == coincideLoginName);

    //        if (a.)
    //        {
    //            isValid = false;
    //        }
    //        else {
    //            isValid = true;
    //        }
            
        
    //    return isValid;
    //}

    public Boolean validateCoincisiveLoginName(string coincideLoginName)
    {
        bool isValid = false;
        var a = from t in da.Employees
                select new
                {
                    t.LoginName,
                };
        foreach (var item in a)
        {
            loginName = item.LoginName;

            if (loginName == coincideLoginName)
            {
                return false;
            }
            if (loginName != coincideLoginName)
            {
                return true;
            }
        }

        return isValid;
    }

    public Boolean validateCoincisiveEmail(string coincideEmail)
    {
        bool isValid = false;
        var a = from t in da.Employees
                select new
                {
                    t.Email,
                };
        foreach (var item in a)
        {
            email = item.Email;

            if (email == coincideEmail)
            {
                return false;
            }
            if (email != coincideEmail)
            {
                return true;
            }
        }

        return isValid;
    }

    public String getMD5Password(string password)
    {
        password = "123456";

        string str_md5 = "";
        byte[] mang = System.Text.Encoding.UTF8.GetBytes(password);

        MD5CryptoServiceProvider my_md5 = new MD5CryptoServiceProvider();
        mang = my_md5.ComputeHash(mang);

        foreach (byte b in mang)
        {
            str_md5 += b.ToString("X2");
        }

        return str_md5;
    }
}